db recovery after raid5 failure

--=-hbBdEmMjSjXSVsGF5XOJ
Content-Type: text/plain; charset=iso-8859-2
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hello

I have serious problems recovering our db after recent raid5 failure.
Long story short - no recent dumps, some missing files (like pg_control).

long version of the story:
our raid failed.. badly. I was able to recover most of the files but some
(like pg_control) are missing (possibly more, I dont know that).
After creating an img of damged raid I copied all recovered files to new
system with same version of PG installed (8.2).
I copied whole DATA folder and tried to run pg service (win xp).
First error was sth about missing pg_control file. I googled solution
involving unsing "pg_resetxlog -f ..\data". That went good (I guess). File
was created.
Second error was sth about 'access denied' to pg_control. It occured that
copying files messed up files ownership so I granted 'rwx' permission to al=
l
users. That went good (I guess).

PG service started at last... but...
When I log in using pgadmin I can see 0 (zero) databases and 0 registered
roles. But when I hit 'refresh' after few second I can see SOME of my old
databases back on list. (registered roles as well). Then after few more
seconds of hitting 'refresh' more and more databases are back on the list.
BUT...
There are no tables inside :( All databases contain only 4 pg_xxxx tables.

Out of pure curiosity I tried to recover one of the databases using some
very old dump using "psql dbname "create table blabla... fields etc" - > 'this relation already exist".
So... is it still there? why cant I see it? any way to recover it?

one more thing I noticed in pgadmin: owner of database "unknown (oid 17004)=
"
but in "registered roles" I can see "name: tom, oid:17004... etc"
So it looks like there is registered owner with the same oid but for some
reason pg cant find a link.

oh, I almost forgot, pg_log is full of
" xlog flush request (number) is not satisfied --- flushed only to
(another_number)
writing block 1 of (another_number)
multiple failures --- write error may be permanent"


Anyone can help? 2 days of using google didnt help much :( YOU are my last
hope...

qc

--=-hbBdEmMjSjXSVsGF5XOJ
Content-Type: text/html; charset=iso-8859-2
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable



2">


Hello

I have serious problems recovering our db after r=
ecent raid5 failure.
Long story short - no recent dumps, some missing fi=
les (like pg_control).

long version of the story:
our raid failed=
... badly. I was able to recover most of the files but some (like pg_control=
) are missing (possibly more, I dont know that).
After creating an img o=
f damged raid I copied all recovered files to new system with same version =
of PG installed (8.2).
I copied whole DATA folder and tried to run pg se=
rvice (win xp).
First error was sth about missing pg_control file. I goo=
gled  solution involving unsing "pg_resetxlog -f ..\data". That went g=
ood (I guess). File was created.
Second error was sth about 'access deni=
ed' to pg_control. It occured that copying files messed up files ownership =
so I granted 'rwx' permission to all users. That went good (I guess).
r>PG service started at last... but...
When I log in using pgadmin I can=
see 0 (zero) databases and 0 registered roles. But when I hit 'refresh' af=
ter few second I can see SOME of my old databases back on list. (registered=
roles as well).  Then after few more seconds of hitting 'refresh'&nbs=
p; more and more databases are back on the list.
BUT...
There are no =
tables inside :( All databases contain only 4 pg_xxxx tables.

Out of=
pure curiosity I  tried to recover one of the databases using some ve=
ry old dump using "psql dbname <dbfile" and got tons of errors saying "c=
reate table blabla... fields etc" - > 'this relation already exist".
=
So... is it still there? why cant I see it? any way to recover it?

o=
ne more thing I noticed in pgadmin: owner of database "unknown (oid 17004)"=
but in "registered roles" I can see "name: tom, oid:17004... etc"
So it=
looks like there is registered owner with the same oid but for some reason=
pg cant find a link.

oh, I almost forgot, pg_log is full of
" x=
log flush request (number) is not satisfied --- flushed only to (another_nu=
mber)
writing block 1 of (another_number)
multiple failures --- write=
error may be permanent"


Anyone can help? 2 days of using google=
didnt help much :( YOU are my last hope...

qc


--=-hbBdEmMjSjXSVsGF5XOJ--

Re: db recovery after raid5 failure

wrote:

> I have serious problems recovering our db after recent raid5
> failure. Long story short - no recent dumps, some missing files
> (like pg_control).

Been there -- at least on the end of helping with recovery for
people in that position with a different database product. It can
be very painful. :-( (I'm assuming from your post that a second
drive failed before recovery from failure of the first was
complete.)

First a word of advice -- don't discard anything. Keep any backups,
keep the bad drives, keep any logs, exports, reports, or anything
else which might contain fragments of the data. Make a backup of
what you have now, if you haven't already. Keep these things for a
long time.

Second, a word of encouragement -- given all these scattered
fragments and enough time and money, you'd might be surprised at how
much data can be recovered. But time and money is required --
someone has to make the hard call of how much money it is worth to
recover how much of the data.

Based on your description, it sounds like you will probably need the
assistance of an outside company to recover very much. Possibly one
company specializing in recovery of data off of damaged disks, and
another for PostgreSQL internals expertise.

I don't suppose there's another source for the data to avoid
attempting this recovery?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db recovery after raid5 failure

--_c2f393bf-d0f6-4b72-b53f-fc59107396d7_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


If the database is not extremely huge=2C makes you wonder what does a RAID =
actually give us. A robust near-realtime replication setup (say PITR + clou=
d) may be good enough against once in a few years of disk failure.atleast y=
ou don't add another point of failure that you (your database/OS) can't do =
anything about.

> Date: Mon=2C 21 Jun 2010 15:30:45 -0500
> From: Kevin.Grittner@wicourts.gov
> To: pgsql-admin@postgresql.org=3B qcor@vp.pl
> Subject: Re: [ADMIN] db recovery after raid5 failure
>=20
> wrote:
> =20
> > I have serious problems recovering our db after recent raid5
> > failure. Long story short - no recent dumps=2C some missing files
> > (like pg_control).
> =20
> Been there -- at least on the end of helping with recovery for
> people in that position with a different database product. It can
> be very painful. :-( (I'm assuming from your post that a second
> drive failed before recovery from failure of the first was
> complete.)
> =20
> First a word of advice -- don't discard anything. Keep any backups=2C
> keep the bad drives=2C keep any logs=2C exports=2C reports=2C or anything
> else which might contain fragments of the data. Make a backup of
> what you have now=2C if you haven't already. Keep these things for a
> long time.
> =20
> Second=2C a word of encouragement -- given all these scattered
> fragments and enough time and money=2C you'd might be surprised at how
> much data can be recovered. But time and money is required --
> someone has to make the hard call of how much money it is worth to
> recover how much of the data.
> =20
> Based on your description=2C it sounds like you will probably need the
> assistance of an outside company to recover very much. Possibly one
> company specializing in recovery of data off of damaged disks=2C and
> another for PostgreSQL internals expertise.
> =20
> I don't suppose there's another source for the data to avoid
> attempting this recovery?
> =20
> -Kevin
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

=20
____________________________________________________________ _____
Hotmail has tools for the New Busy. Search=2C chat and e-mail from your inb=
ox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_1=

--_c2f393bf-d0f6-4b72-b53f-fc59107396d7_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






If the database is not extremely huge=2C makes you wonder what does a RAID =
actually give us. A robust near-realtime replication setup (say PITR + clou=
d) may be good enough against once in a few years of disk failure.

Re: db recovery after raid5 failure

Balkrishna Sharma wrote:

> If the database is not extremely huge, makes you wonder what does
> a RAID actually give us.

Well, RAID5 gives you a situations where you must have a second
drive fail before recovery for the first failure is complete, versus
being instantly dead on a single-drive failure. RAID6 requires
three drives to fail in close succession (assuming a hot spare which
initiates recovery on failure). RAID10 requires that two paired
drives fail. We have about 100 database servers, and probably
average about two drive failures a month; having any down time from
them is rare because of RAID (and that's with us primarily using
RAID5).

> A robust near-realtime replication setup (say PITR + cloud)
> may be good enough against once in a few years of disk
> failure.atleast you don't add another point of failure that you
> (your database/OS) can't do anything about.

You've totally lost me there. "The cloud" still uses similar
techniques, just out of your sight and control. If you assume that
whoever is running it can do it better than you can, that's one
thing; just don't assume it's magic. The machines in my shop are
what I *can* do something about. Management here insists on near-
real-time backup using at least two completely independent
techniques to multiple machines in multiple buildings, with
continuous testing that all backups actually restore. If we were to
float data off into a cloud somewhere, I can guarantee we wouldn't
count on it without an alternative. As a place to put "one more
copy" it might make sense, as long as it had strong encryption.
(Again, you've lost all control over who has what access once you
send it into the cloud.)

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db recovery after raid5 failure

--_37603ea1-c634-4ccc-9889-dd83b3e133c7_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


> average about two drive failures a monthYou must be having a real huge po=
stgres setup with several hundreds of drives to have such high frequency of=
failure.

> As a place to put "one more> copy" it might make sense=2C as long as it h=
ad strong encryption.I didn't expand but that's what I meant. The copy in c=
loud to be your final resort incase the LAN and the WAN copy both fail. You=
get an extra copy in a different geographic location for some catastrophic=
event.



> Date: Tue=2C 22 Jun 2010 09:46:46 -0500
> From: Kevin.Grittner@wicourts.gov
> To: b_ki@hotmail.com=3B pgsql-admin@postgresql.org=3B qcor@vp.pl
> Subject: Re: [ADMIN] db recovery after raid5 failure
>=20
> Balkrishna Sharma wrote:
> =20
> > If the database is not extremely huge=2C makes you wonder what does
> > a RAID actually give us.
> =20
> Well=2C RAID5 gives you a situations where you must have a second
> drive fail before recovery for the first failure is complete=2C versus
> being instantly dead on a single-drive failure. RAID6 requires
> three drives to fail in close succession (assuming a hot spare which
> initiates recovery on failure). RAID10 requires that two paired
> drives fail. We have about 100 database servers=2C and probably
> average about two drive failures a month=3B having any down time from
> them is rare because of RAID (and that's with us primarily using
> RAID5).
> =20
> > A robust near-realtime replication setup (say PITR + cloud)
> > may be good enough against once in a few years of disk
> > failure.atleast you don't add another point of failure that you
> > (your database/OS) can't do anything about.
> =20
> You've totally lost me there. "The cloud" still uses similar
> techniques=2C just out of your sight and control. If you assume that
> whoever is running it can do it better than you can=2C that's one
> thing=3B just don't assume it's magic. The machines in my shop are
> what I *can* do something about. Management here insists on near-
> real-time backup using at least two completely independent
> techniques to multiple machines in multiple buildings=2C with
> continuous testing that all backups actually restore. If we were to
> float data off into a cloud somewhere=2C I can guarantee we wouldn't
> count on it without an alternative. As a place to put "one more
> copy" it might make sense=2C as long as it had strong encryption.=20
> (Again=2C you've lost all control over who has what access once you
> send it into the cloud.)
> =20
> -Kevin
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

=20
____________________________________________________________ _____
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_37603ea1-c634-4ccc-9889-dd83b3e133c7_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Re: db recovery after raid5 failure

Balkrishna Sharma wrote:

>> average about two drive failures a month

> You must be having a real huge postgres setup with several hundreds
> of drives to have such high frequency of failure.

About 100 database servers with over 1000 drives spinning 24/7. Also
probably significant, management-set policy is to replace machines
after four years, and we don't always hit that. I haven't tried to
run numbers on it, but the pattern sure seems to match published
reports that we have some initial failures within the first few
months when a set of machines go in, it settles down for about three
years, then the failure rate starts to edge inexorably upward.

>> As a place to put "one more copy" it might make sense, as long as
>> it had strong encryption.

> I didn't expand but that's what I meant. The copy in cloud to be
> your final resort incase the LAN and the WAN copy both fail. You
> get an extra copy in a different geographic location for some
> catastrophic event.

OK, that makes sense.

-Kevin



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin